package fr.uniscol.siecle.dao.impl;

import java.util.List;

import org.springframework.stereotype.Repository;

import fr.uniscol.dao.UniscolDaoSupport;
import fr.uniscol.dao.impl.SQLNames;
import fr.uniscol.model.BaseAnneeScolaire;
import fr.uniscol.model.RapportTraitement;
import fr.uniscol.siecle.dao.IDonneesSIECLEElevesDao;
import fr.uniscol.siecle.dao.IImportDonneesSIECLEDao;
import fr.uniscol.siecle.dao.mapper.EleveRowMapper;
import fr.uniscol.siecle.dao.mapper.OptionEleveRowMapper;
import fr.uniscol.siecle.dao.mapper.StructureEleveRowMapper;
import fr.uniscol.siecle.model.Division;
import fr.uniscol.siecle.model.Eleve;
import fr.uniscol.siecle.model.Eleves;
import fr.uniscol.siecle.model.FichierSIECLE;
import fr.uniscol.siecle.model.Groupe;
import fr.uniscol.siecle.model.Option;
import fr.uniscol.siecle.model.OptionEleve;
import fr.uniscol.siecle.model.StructureEleve;
import org.apache.log4j.Logger;

@Repository("donneesElevesDao")
public class DonneesSIECLEElevesDaoImpl extends UniscolDaoSupport implements IDonneesSIECLEElevesDao, IImportDonneesSIECLEDao {

    public static Logger log = Logger.getLogger(DonneesSIECLEElevesDaoImpl.class);
    
	private Eleve findEleveFromList(String id, List<Eleve> eleves){
		for(Eleve e : eleves){
			if(e.getId().equalsIgnoreCase(id)){
				return e;
			}
		}
		return null;
	}
	
	@Override
	public Eleves getDonneesEleves(int annee, long etabId) {
		
		Eleves eleves = new Eleves(); 
		
		List<Eleve> eles = getJdbcTemplate().query("SELECT * FROM bee_eleve WHERE etablissementid=? AND annee=?", new Object[]{
			etabId,
			annee
		}, new EleveRowMapper());
		
		eleves.setEleves(eles);
		
		// options
		List<OptionEleve> options = getJdbcTemplate().query("SELECT * FROM bee_option_eleve WHERE etablissementid=? AND annee=? ", new Object[]{
			etabId,
			annee
		},
		new OptionEleveRowMapper());
		eleves.setOptionsEleve(options);
		
		// Structures
		List<StructureEleve> structures = getJdbcTemplate().query("SELECT * FROM bee_structure_eleve WHERE etablissementid=? AND annee=? ", new Object[]{
			etabId,
			annee
		}, 
		new StructureEleveRowMapper());
		eleves.setStructuresEleve(structures);
		
		return eleves;
	}

	@Override
	public boolean saveFichierSIECLE(BaseAnneeScolaire base, FichierSIECLE fichier, RapportTraitement rapport) {
		
		Eleves eleves = (Eleves) fichier;
		/*
		String reqUpdate = "UPDATE bee_eleves SET date_export=?, horodatage=? WHERE annee=? AND etablissementid=? ";
		String reqInsert = "INSERT bee_eleves (annee, etablissementid, date_export, horodatage) VALUES(?, ?, ?, ?)";
		
		int nbRowsAffected = getJdbcTemplate().update(reqUpdate, new Object[]{
			eleves.getParametres().getDateExport(),
			eleves.getParametres().getHorodatage(),
			base.getAnnee(), 
			base.getEtablissement().getId()
		});
		if(nbRowsAffected == 0){
			nbRowsAffected = getJdbcTemplate().update(reqInsert, new Object[]{
				base.getAnnee(), 
				base.getEtablissement().getId(),
				eleves.getParametres().getDateExport(),
				eleves.getParametres().getHorodatage()
			});
		}
		
		if(nbRowsAffected != 1){
			throw new NullPointerException("Nombre de lignes mises à jour incorrect !");
		}
		
		getJdbcTemplate().update("DELETE FROM bee_option_eleve WHERE annee=? AND etablissementid=? ", new Object[]{
			base.getAnnee(), 
			base.getEtablissement().getId(),
		});
		getJdbcTemplate().update("DELETE FROM bee_structure_eleve WHERE annee=? AND etablissementid=? ", new Object[]{
			base.getAnnee(), 
			base.getEtablissement().getId(),
		});
		getJdbcTemplate().update("DELETE FROM bee_eleve WHERE annee=? AND etablissementid=? ", new Object[]{
			base.getAnnee(), 
			base.getEtablissement().getId(),
		});
		*/
        
		boolean enregistrementElevesOK = true;
		for(Eleve e : eleves.getEleves()){
            if(e.getMef().getCode() != null){
                enregistrementElevesOK &= saveEleve(base, eleves, e);
            }
		}
		
		return enregistrementElevesOK;
	}

	private boolean saveEleve(BaseAnneeScolaire base, Eleves eleves, Eleve e) {
        
        boolean nouvelEleve = false;
        
        int nbRowsAffected = getJdbcTemplate().update("UPDATE uni_eleve " +
                                                      "SET nom=?, prenom=?, ine=? "
                                                    + "WHERE baseid=? AND id_siecle=?", new Object[]{
                                                       e.getNom(),
                                                       e.getPrenom(),
                                                       e.getId_national(),
                                                       base.getId(),
                                                       e.getId()
                                                    });
        
        if(nbRowsAffected == 0){
            nbRowsAffected = getJdbcTemplate().update("INSERT INTO uni_eleve (baseid, nom, prenom, ine, id_siecle) VALUES(?,?,?,?,?)", new Object[]{
               base.getId(),
               e.getNom(),
               e.getPrenom(),
               e.getId_national(),
               e.getId()
            });             
            
            nouvelEleve = true;
        }
        
        if(nbRowsAffected == 1 && e.getMef() != null && e.getMef().getCode() != null){
            nbRowsAffected = getJdbcTemplate().update("UPDATE uni_scolarite_eleve "
                                                    + "SET mefid=getMefByCodeSiecle(?, ?) "
                                                    + "WHERE EXISTS(SELECT 1 "
                                                    + "             FROM uni_eleve e "
                                                    + "             WHERE e.eleveid=eleveid "
                                                    + "             AND e.scolariteactuelleid=scolariteid "
                                                    + "             AND e.id_siecle=?)"
                                                    + "", new Object[]{
                                                      base.getId(),
                                                      e.getMef().getCode(),
                                                      e.getId()
                                                    });
            
            if(nbRowsAffected == 0){
                
                log.debug("INSERT INTO uni_scolarite_eleve (eleveid, mefid, debut) VALUES(getEleveIdByIdSiecle(" +  base.getId()+ ", " + e.getId() + "), getMefByCodeSiecle("+ base.getId() +" ," + e.getMef().getCode() + "), NOW())");
                
                nbRowsAffected = getJdbcTemplate().update("INSERT INTO uni_scolarite_eleve (eleveid, mefid, debut) "
                                                        + "VALUES(getEleveIdByIdSiecle(?, ?), getMefByCodeSiecle(?,?), NOW())", new Object[]{
                   base.getId(),
                   e.getId(),
                   base.getId(),
                   e.getMef().getCode()
                });
                
                if(nbRowsAffected == 1){
                    long lastScolariteId = getJdbcTemplate().queryForObject(SQLNames.getLastInsertIdQuery(), Long.class);  
                    
                    log.debug("ScolariteId créee: " + lastScolariteId);
                    
                    nbRowsAffected = getJdbcTemplate().update("UPDATE " + SQLNames.TABLE_ELEVE + " SET scolariteactuelleid=? WHERE id_siecle=? ", new Object[]{
                       lastScolariteId,
                       e.getId()
                    });
                }
            }
        }
        
		/*
		int nbRowsAffected = getJdbcTemplate().update("INSERT INTO bee_eleve (annee, etablissementid, id, elenoet,"
				+ "															  id_national, nom, prenom, date_naissance, doublement, code_mef, "
				+ "															  code_pays, accepte_sms, date_modification, date_sortie, code_regime, date_entree, "
				+ "															  ville_naissance, code_motif_sortie, code_sexe, code_pays_nat, adhesion_transport, code_provenance)"
				+ "										VALUES(?, ?, ?, ?,"
				+ "															  ?, ?, ?, ?, ?, ?, "
				+ "															  ?, ?, ?, ?, ?, ?, "
				+ "															  ?, ?, ?, ?, ?, ?)", new Object[]{
						base.getAnnee(), 
						base.getEtablissement().getId(),
						e.getId(),
						e.getElenoet(),
						e.getId_national(),
						e.getNom(),
						e.getPrenom(),
						e.getDate_naissance(),
						e.getDoublement(),
						e.getMef().getCode(),
						e.getCode_pays(),
						e.getAccepte_sms(),
						e.getDate_modification(),
						e.getDate_sortie(),
						e.getRegime(),
						e.getDate_entree(),
						e.getVille_naissance(),
						e.getMotifSortie().getCode(),
						e.getCode_sexe(),
						e.getCode_pays_nat(),
						e.getAdhesion_transport(),
						e.getProvenance().getCode()
				});
		*/
		if(nbRowsAffected != 1){
			throw new NullPointerException("Nombre de lignes mises à jour incorrect !");
		}
        
        if(!nouvelEleve){
            getJdbcTemplate().update("DELETE FROM uni_option_eleve WHERE EXISTS(SELECT 1 FROM uni_eleve e WHERE e.scolariteactuelleid=scolariteid AND e.id_siecle=?)", new Object[]{
                e.getId()
            });
        }
        
		boolean retour = true;
		
		for(Option opt : e.getOptions()){
			retour = saveOptionEleve(base, eleves, e, opt);
		}
		for(Division st : e.getStructures()){
			retour &= saveStructureEleve(base, eleves, e, st);
		}
		
		return retour;
	}

	private boolean saveOptionEleve(BaseAnneeScolaire base, Eleves eleves, Eleve e, Option opt) {
		
        log.debug("OPTION: modalite=" + opt.getModalite().getCode());
        
        long scolariteId = getJdbcTemplate().queryForObject("SELECT getScolariteIdByEleveSiecle(?,?)", new Object[]{
            base.getId(),
            e.getId(),
        }, Long.class);
        
        if(scolariteId == 0){
            return false;
        }
        
        int nbRowsAffected = getJdbcTemplate().update("INSERT INTO uni_option_eleve (scolariteid, matiereid, modalite, debut) VALUES(getScolariteIdByEleveSiecle(?,?),getMatiereIdByIdSiecle(?,?),?,NOW())", new Object[]{
            base.getId(),
            e.getId(),
            base.getId(),
            opt.getMatiere().getCode(),
            opt.getModalite().getCode()
        });
        
		/*int nbRowsAffected = getJdbcTemplate().update("INSERT INTO bee_option_eleve (annee, etablissementid, eleve_id, code_matiere, code_modalite, num_option)"
				+ "										VALUES(?, ?, ?, ?, ?, ?)", new Object[]{
					base.getAnnee(), 
					base.getEtablissement().getId(), 
					e.getId(),
					opt.getMatiere().getCode(),
					opt.getModalite().getCode(),
					opt.getNum_option()
				});*/
		
		return nbRowsAffected == 1;
	}

	private boolean saveStructureEleve(BaseAnneeScolaire base, Eleves eleves, Eleve e, Division st) {
		
        int nbRowsAffected = getJdbcTemplate().update("UPDATE uni_scolarite_eleve SET structureid=getStructureIdByIdSiecle(?,?) WHERE EXISTS(SELECT 1 FROM uni_eleve e WHERE e.scolariteactuelleid=scolariteid AND e.id_siecle=?)",
            new Object[]{
               base.getId(),
               st.getCode(),
               e.getId()
            });
        
		/*int nbRowsAffected = getJdbcTemplate().update("INSERT INTO bee_structure_eleve (annee, etablissementid, eleve_id, code_structure, type_structure)"
				+ "										VALUES(?, ?, ?, ?, ?)", new Object[]{
					base.getAnnee(), 
					base.getEtablissement().getId(), 
					e.getId(),
					st.getCode(),
					(st instanceof Groupe) ? "GP" : "DI"
				});*/
        
		return nbRowsAffected == 1;
	}
}
